﻿using Crawler.DBEntities;
using Crawler.WebEntities;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Crawler.DataHelper
{
    class MysqlHelper
    {
        //private const String connStr = String.Format("server={0};user id={1}; password={2}; database=mydb; pooling=false", "localhost", "root", "123456");
        private string connStr = String.Format("server={0};user id={1}; password={2}; database=mydb; pooling=false", "localhost", "root", "123456");
        //查找所有的种子信息
        private const string sel_allSeed = @"SELECT * FROM info_seed";
        //查看新插入的记录的id
        private string sel_lastInsert = @"select LAST_INSERT_ID()";
        /// <summary>
        /// 删除所有的已访问记录
        /// </summary>
        public void Del_All_Visited()
        {
            string delAllVisited_sql = "DELETE FROM info_visited";
            bool isSucceedDeleted = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedDeleted = Transaction(conn, delAllVisited_sql);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedDeleted = false;
            }
        }
        /// <summary>
        /// 获取所有的种子记录
        /// </summary>
        /// <returns></returns>
        public List<Seed> GetAllSeeds()
        {
            List<Seed> seeds = new List<Seed>();
            // 建立数据库连接.
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_allSeed, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                string seedUrl = testRow["seed_url"].ToString();
                Seed seed = new Seed(seedUrl);
                seeds.Add(seed);
            }
            return seeds;
        }
        /// <summary>
        /// 表中是否有该post
        /// </summary>
        /// <param name="postUrl"></param>
        /// <returns></returns>
        public bool IsPostExist(string postUrl)
        {
            
            string sel_certainPost =string.Format(@"SELECT * FROM info_post where post_url ='{0}'",postUrl);
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_certainPost, conn);
            DataSet testDataSet = new DataSet();
            adapter.Fill(testDataSet, "result_data");
            conn.Close();
            if (testDataSet.Tables["result_data"].Rows.Count == 0)
                return false;
            else
                return true;
        }
        /// <summary>
        /// 向表中添加post记录
        /// </summary>
        /// <param name="post"></param>
        /// <returns></returns>
        public bool InsertPost(Post post)
        {
            string fk_seedid = post.Seed_post_id.ToString();
            string fk_forumid = post.Forum_post_id.ToString();
            string url = post.Url;
            DateTime date = post.Date;
            string title = post.Title;
            string passage = post.Passage;                    
            DateTime createTime = post.CreateTime;   //dateTime 的获取
            string insertPost_sql = string.Format(@"INSERT INTO info_post (FK_forum_post_id, FK_seed_post_id, post_url,post_date,post_title,post_passage,post_createTime) 
            VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')",
                fk_forumid, fk_seedid, url, createTime, title, passage, DateTime.Now);
            bool isSucceedInsert = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedInsert = Transaction(conn, insertPost_sql);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedInsert = false;
            }
            return isSucceedInsert;
        }
        /// <summary>
        /// 获取新插入的数据的id
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private int GetInsertID(MySqlConnection conn)
        {
            int id = -1;
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_lastInsert, conn);
            DataSet testDataSet = new DataSet();
            adapter.Fill(testDataSet, "result_data");

            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                var arr = testRow.ItemArray;
                id = int.Parse(testRow["LAST_INSERT_ID()"].ToString());
            }
            return id;
        }
        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="conn"></param>
        private bool Transaction(MySqlConnection conn, string insert_sql)
        {
            MySqlTransaction t = conn.BeginTransaction();
            bool isTranSucceed = true;
            try
            {
                Insert_2(conn, t, insert_sql);
                t.Commit();
            }
            catch (Exception)
            {
                t.Rollback();
                isTranSucceed = false;
            }
            return isTranSucceed;
        }

        /// <summary>
        /// 插入数据.
        /// </summary>
        /// <param name="conn"></param>
        private void Insert_2(MySqlConnection conn, MySqlTransaction t, string insert_sql)
        {
            // 创建一个 Command.
            MySqlCommand insertCommand = conn.CreateCommand();

            // 定义需要执行的SQL语句.
            insertCommand.CommandText = insert_sql;
            // 注意： 只有加了这一句， 才能事务处理！！！
            insertCommand.Transaction = t;
            int insertRowCount = insertCommand.ExecuteNonQuery();
        }
        
        /// <summary>
        /// 新纪录插入到visited表
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool InsertVisited(MyUri uri)
        {
            //string.Format(@"INSERT INTO info_seed (seed_url,seed_lastUpdateTime,seed_createTime) VALUES ('{0}','{1}','{2}')", uri.AbsoluteUri, DateTime.Now, seed.CreateTime);
            string ins_visited = string.Format(@"INSERT INTO info_visited  (visited_url,visited_time) VALUES('{0}','{1}')", uri.AbsoluteUri,DateTime.Now);
            bool isSucceedInsert = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedInsert = Transaction_Visited(conn, ins_visited);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedInsert = false;
            }
            return isSucceedInsert;
        }
        /// <summary>
        /// 插入到visited表中的数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="insertVisited_sql"></param>
        /// <returns></returns>
        private bool Transaction_Visited(MySqlConnection conn, string insertVisited_sql)
        {
            MySqlTransaction t = conn.BeginTransaction();
            bool isTranSucceed = true;
            try
            {
                InsertVisited(conn, t, insertVisited_sql);
                t.Commit();
            }
            catch (Exception)
            {
                t.Rollback();
                isTranSucceed = false;
            }
            return isTranSucceed;
        }
        /// <summary>
        /// 插入Visited数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="t"></param>
        /// <param name="insert_sql"></param>
        private void InsertVisited(MySqlConnection conn, MySqlTransaction t, string insert_sql)
        {
            // 创建一个 Command.
            MySqlCommand insertCommand = conn.CreateCommand();
            insertCommand.CommandText = insert_sql;
            insertCommand.Transaction = t;
            int insertRowCount = insertCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// 判断种子是否过期
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsSeedTimeOut(MyUri uri)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();
            MySqlDataAdapter IsSeedTimeOut = new MySqlDataAdapter(string.Format(@"select seed_lastUpdateTime from info_seed where seed_url = '{0}'",uri.AbsoluteUri), conn);
            DataSet mySql = new DataSet();
            IsSeedTimeOut.Fill(mySql);
            DataTable tbl = mySql.Tables[0];
            DataRow irow = tbl.Rows[0];
            DateTime timestamp = (DateTime)irow[0];
            TimeSpan d3 = DateTime.Now.Subtract(timestamp);
            conn.Close();

            if (d3.Days > 14)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 更新种子记录
        /// </summary>
        /// <param name="seed"></param>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool UpdateSeed(Seed seed, ref MyUri uri)
        {
            string update_seed = string.Format(@"UPDATE info_seed 
                                                    SET seed_lastUpdateTime = '{0}'
                                                    WHERE seed_url='{1}'", System.DateTime.Now, uri.AbsoluteUri);
            bool isSucceedUpdate = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedUpdate = Transaction_Visited(conn, update_seed);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedUpdate = false;
            }
            return isSucceedUpdate;
        }
        /// <summary>
        /// 插入种子记录
        /// </summary>
        /// <param name="seed"></param>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool InsertSeed(ref MyUri uri, string postContentRegex)
        {
            bool isInsertSucceed = true;

            MySqlConnection conn = new MySqlConnection(connStr);
            string insertSeed_sql = string.Format(@"INSERT INTO info_seed (seed_url,seed_lastUpdateTime,seed_createTime,seed_postCon_regex) VALUES ('{0}','{1}','{2}','{3}')", uri.AbsoluteUri, DateTime.Now, DateTime.Now,postContentRegex);
            try
            {
                conn.Open();
                isInsertSucceed = Transaction(conn, insertSeed_sql);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isInsertSucceed = false;
            }
            return isInsertSucceed;
        }
        
       
        /// <summary>
        /// 查找所有和所给的种子uri相关的板块uri
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public List<MyUri> SelectForums(MyUri uri)
        {
            List<MyUri> forums = new List<MyUri>();
            string selectForums_sql = string.Format(@"select forum_url 
                            from info_forum,info_seed 
                            where info_forum.Fk_seed_forum_id = info_seed.seed_id and info_seed.seed_url = '{0}'", uri.AbsoluteUri);
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(selectForums_sql, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                string forumUrl = testRow["forum_url"].ToString();
                MyUri forum = new MyUri(forumUrl);
                forums.Add(forum);
            }
            return forums;
        }
        /// <summary>
        /// 板块uri是否在表中存在
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsForumExist(MyUri uri)
        {
            string sel_forum = string.Format("select * from info_forum where forum_url = '{0}'", uri.AbsoluteUri);
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_forum, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            if (testDataSet.Tables["result_data"].Rows.Count>0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 更新板块的信息
        /// </summary>
        /// <param name="uri"></param>
        /// <param name="forum"></param>
        /// <returns></returns>
        public bool UpdateForum(ref MyUri uri, Forum forum)
        {
            string update_forum = string.Format(@"UPDATE info_forum
                                                SET forum_lastUpdateTime = '{0}'
                                                WHERE forum_url='{1}'", System.DateTime.Now, uri.AbsoluteUri);
            bool isSucceedUpdate = true;
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
                isSucceedUpdate = Transaction_Visited(conn, update_forum);
                conn.Close();
            }
            catch (Exception)
            {
                isSucceedUpdate = false;
            }
            return isSucceedUpdate;
        }
        /// <summary>
        /// 在表中插入新增的板块记录
        /// </summary>
        /// <param name="uri"></param>
        /// <param name="forum"></param>
        /// <returns></returns>
        public bool InsertForum(ref MyUri uri, Forum forum)
        {
                    
           bool isInsertSucceed = true;

            MySqlConnection conn = new MySqlConnection(connStr);
            string insertForum_sql = string.Format(@"INSERT INTO info_forum (forum_url,forum_lastUpdateTime,forum_createTime) VALUES ('{0}','{1}','{2}')", uri.AbsoluteUri, forum.CreateTime,DateTime.Now);
            try
            {
                conn.Open();
                isInsertSucceed = Transaction(conn, insertForum_sql);
                uri.Id = GetInsertID(conn);
                conn.Close();
            }
            catch (Exception)
            {
                isInsertSucceed = false;
            }
            return isInsertSucceed;
        }
        /// <summary>
        /// 判断帖子是否已经被访问过
        /// </summary>
        /// <param name="uri"></param>
        /// <returns></returns>
        public bool IsVisited(MyUri uri)
        {
            int count = 0;
            
            string sel_forum = string.Format(@"select * from info_visited");
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_forum, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                string postUrl = testRow["visited_url"].ToString();
                if (uri.AbsoluteUri.Equals(postUrl))
                    return true;
            }
            return false;
        }
        /// <summary>
        /// 获取种子的帖子内容正则表达式
        /// </summary>
        /// <param name="seedUri"></param>
        /// <returns></returns>
        public string GetPostContentRegex(MyUri seedUri)
        {
            string postConRegex = null;
            string postContentRegex = string.Format(@"select * from info_seed where seed_url = {0}",seedUri.AbsoluteUri);
            // 建立数据库连接.
            MySqlConnection conn = new MySqlConnection(connStr);
            // 创建一个适配器
            MySqlDataAdapter adapter = new MySqlDataAdapter(sel_allSeed, conn);
            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();
            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");
            // 关闭数据库连接.
            conn.Close();
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                postConRegex = testRow["seed_postCon_regex"].ToString();
            }
            return postConRegex;
        }
    }
}
